﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Windows;
using System.Collections.ObjectModel;

namespace CodeSnippetsWPF.Model
{
    public class CodeSnippetsDB
    {
        //  
        private string _connectionString = Properties.Settings.Default.CodeDBConnectionString;

        public ObservableCollection<CodeSnippet> Code_Snippet_Buffer
        {
            get;
            private set;
        }
        public ObservableCollection<string> Code_Language_Buffer
        {
            get;
            private set;
        }
 
        public CodeSnippet GetCodeSnippetByID(int ID)
        {

            SQLiteConnection cnn = new SQLiteConnection("Data Source=" + _connectionString);


            try
            {


                cnn.Open();

                SQLiteCommand mycommand = new SQLiteCommand(cnn);

                mycommand.CommandText = string.Format("SELECT * FROM CODE WHERE CODE_ID={0}", ID);

                SQLiteDataReader reader = mycommand.ExecuteReader();

                if (reader.Read())
                {
                    Console.WriteLine(reader["CODE_ID"]);
                    Console.WriteLine(reader["CODE_TITLE"]);
                    Console.WriteLine(reader["CODE_LANGUAGE"]);
                    Console.WriteLine(reader["CODE_SNIPPET"]);

                    //dbnull can to string,   reader["CODE_LANGUAGE"].ToString() is better than       (string)(reader["CODE_LANGUAGE"])
                    CodeSnippet codesnippet = new CodeSnippet(int.Parse(reader["CODE_ID"].ToString()),

                                                              reader["CODE_TITLE"].ToString(),
                                                             reader["CODE_LANGUAGE"].ToString(),
                                                             reader["CODE_SNIPPET"].ToString(),
                                                             reader["CODE_RAW"].ToString(),
                                                             this

                        );

                    return codesnippet;
                }
                else
                {
                    return null;
                }

            }
            finally
            {
                cnn.Close();

            }


        }


        private ObservableCollection<CodeSnippet> GetCodeSnipetBySQL(string sql)
        {
            ObservableCollection<CodeSnippet> snippetList = new ObservableCollection<CodeSnippet>();
            SQLiteConnection cnn = new SQLiteConnection("Data Source=" + _connectionString);

            try
            {
                cnn.Open();

                SQLiteCommand mycommand = new SQLiteCommand(cnn);

                mycommand.CommandText = string.Format(sql);

                SQLiteDataReader reader = mycommand.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine(reader["CODE_ID"]);
                    Console.WriteLine(reader["CODE_TITLE"]);
                    Console.WriteLine(reader["CODE_LANGUAGE"]);
                    Console.WriteLine(reader["CODE_RAW"]);

                    //dbnull can to string,   reader["CODE_LANGUAGE"].ToString() is better than       (string)(reader["CODE_LANGUAGE"])
                    CodeSnippet codesnippet = new CodeSnippet(int.Parse(reader["CODE_ID"].ToString()),

                                                              reader["CODE_TITLE"].ToString(),
                                                             reader["CODE_LANGUAGE"].ToString(),
                                                             reader["CODE_SNIPPET"].ToString(),
                                                             reader["CODE_RAW"].ToString(),
                                                             this

                        );
                    snippetList.Add(codesnippet);

                }
               
            }
            catch (System.Exception ex)
            {

            }
            finally
            {
                cnn.Close();
            }

            return snippetList;
        }


        private ObservableCollection<string> GetCodeCodeLanguageBySQL(string sql)
        {
            ObservableCollection<string> snippetList = new ObservableCollection<string>();
            SQLiteConnection cnn = new SQLiteConnection("Data Source=" + _connectionString);

            try
            {
                cnn.Open();

                SQLiteCommand mycommand = new SQLiteCommand(cnn);

                mycommand.CommandText = string.Format(sql);

                SQLiteDataReader reader = mycommand.ExecuteReader();

                while (reader.Read())
                {

              snippetList.Add(reader["LANGUAGE"].ToString());

                }

            }
            catch (System.Exception ex)
            {

            }
            finally
            {
                cnn.Close();
            }

            return snippetList;
        }


        public CodeSnippetsDB()
        {
            Code_Snippet_Buffer= new ObservableCollection<CodeSnippet>();
        }

        public void LoadAllData() {
            Code_Snippet_Buffer = GetCodeSnipetBySQL("SELECT * FROM CODE");
          //  Code_Language_Buffer = GetCodeCodeLanguageBySQL("select LANGUAGE from CODE_LANGUAGE order by LANGUAGE_COUNT DESC");
            LoadLanguages();
          }

        public void LoadLanguages()
        {
           // Code_Language_Buffer = GetCodeCodeLanguageBySQL("select * from (  select  CODE_LANGUAGE,count(*) as lcount  from CODE  group by CODE_LANGUAGE )   order by lcount DESC");

            Code_Language_Buffer = GetCodeCodeLanguageBySQL("select LANGUAGE from CODE_LANGUAGE order by LANGUAGE_COUNT DESC");
   
        
        }










        internal void UpdateRow(int _CODE_ID, string p, string value)
        {
            //update CODE set CODE_RAW=002  where CODE_ID=0
            
            string sqlCommand = string.Format(@"update CODE set {2}='{0}'  where CODE_ID={1}",value,_CODE_ID,p);
            ExecuteSQL(sqlCommand);
        }

        private void ExecuteSQL(string sql)
        {
            SQLiteConnection cnn = new SQLiteConnection("Data Source=" + _connectionString);

            try
            {
                cnn.Open();

                SQLiteCommand mycommand = new SQLiteCommand(cnn);

                mycommand.CommandText = sql;
                Console.WriteLine(sql);
                mycommand.ExecuteNonQuery();              

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                cnn.Close();
            }
        }
        int GetMaxIndexFromCode()
        {
            int returnValue = 0;
            SQLiteConnection cnn = new SQLiteConnection("Data Source=" + _connectionString);

            try
            {
                cnn.Open();

                SQLiteCommand mycommand = new SQLiteCommand(cnn);

                mycommand.CommandText = string.Format("select max(CODE_ID) from CODE");

            //    SQLiteDataReader reader = mycommand.ExecuteScalar();
                object value = mycommand.ExecuteScalar();

                if (value!=null)
                {
                    returnValue = int.Parse(value.ToString());                

                }

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                cnn.Close();
            }

            return returnValue;

        }

        public void NewCodeSnippet()
        {
            int theNewIntex = GetMaxIndexFromCode() + 1;

            string sqlCommand = "INSERT INTO CODE (\"CODE_ID\",\"CODE_LANGUAGE\",\"CODE_TITLE\",\"CODE_SNIPPET\",\"CODE_RAW\") VALUES (" + theNewIntex + ", \"\",\"\" ,\"\",\"\")";
            Console.Write(sqlCommand);
            ExecuteSQL(sqlCommand);
            Code_Snippet_Buffer = GetCodeSnipetBySQL("SELECT * FROM CODE WHERE CODE_ID="+theNewIntex);
        //    LoadLanguages();
        }

        public void SearchByKeyWord(string sql,string language)
        {
            //x LIKE '%' + @Value + '%'

            string sqlcommand = BuildSQLSearch(sql, language);      
            Code_Snippet_Buffer = GetCodeSnipetBySQL(sqlcommand);

        }

        private string NoMoreSpace(string input)
        {
            while (input.Contains("  "))
            {
                input = input.Replace("  ", " ");
            }
            return input;
        }
        public bool InsertNewLanguage(string language)
        {
            if (language=="")
            {
                return false;
            }

            language = language.ToUpper().Trim();

            if (Code_Language_Buffer.Contains(language))
            {
                return false;
            }
            string sqlCommand = "INSERT INTO CODE_LANGUAGE (\"LANGUAGE\") VALUES (\"" + language + "\")";

            ExecuteSQL(sqlCommand);

            Code_Language_Buffer.Add(language);


            return true;
        }
        private string BuildSQLSearch(string words, string language)
        {
            
            if (words=="" && language=="")
            {
                return "select * from CODE";
            }
            if (words=="" && language!="")
            {
                return "select * from CODE where CODE_LANGUAGE=\"" + language + "\"";
            }
            string returnStr = "select * from CODE where ";
            words = NoMoreSpace(words);

            if (words.Contains(" "))
            {
                returnStr += "(";
                string[] word = words.Split(new char[]{' '}, StringSplitOptions.RemoveEmptyEntries);
                for (int i = 0; i < word.Length;i++ )
                {
                    if (i!=0)
                    {
                        returnStr += " OR ";
                    }
                    returnStr += "CODE_TITLE LIKE \"%" + word[i] + "%\" ";
                    returnStr += "OR CODE_RAW LIKE \"%" + word[i] + "%\" ";



                }
                returnStr+=")";

            }
            else
            {
                returnStr += "( CODE_TITLE LIKE \"%" + words + "%\" ";
                returnStr += "OR CODE_RAW LIKE \"%" + words + "%\") ";
            }

            if (language!="")
            {
                returnStr += "AND CODE_LANGUAGE=\"" + language + "\"";
            }


            Console.WriteLine(returnStr);
            return returnStr;
        }
    }
}
